-- @owner: @zhanghuan96
-- @date: 2025/4/22
-- @testpoint: rotate语法中IN子句中的列名使用动态SQL生成的列
@conn PrimaryDbDmode;
--step1:建表;expect:成功
drop table if exists t_rotate0016;
CREATE TABLE t_rotate0016 (year INT,  product VARCHAR(50),  amount DECIMAL(10, 2));
INSERT INTO t_rotate0016 (year, product, amount) VALUES
(2020, 'A', 100),
(2020, 'B', 200),
(2021, 'A', 150),
(2021, 'B', 250);
--step2:创建自定义函数;expect:成功
CREATE OR REPLACE FUNCTION func_rotate0016()
RETURNS TABLE (year INT, a NUMERIC, b NUMERIC)
AS $$
DECLARE
    cols TEXT;
    sql TEXT;
BEGIN
    SELECT STRING_AGG(DISTINCT product, ', ')
    INTO cols
    FROM t_rotate0016;
    sql := '
        SELECT *
        FROM (
            SELECT year, product, amount
            FROM t_rotate0016
        ) AS source_table
        rotate (
            SUM(amount)
            FOR product IN (' || cols || ')
        ) AS pivot_table;';
    RETURN QUERY EXECUTE sql;
END;
$$ LANGUAGE plpgsql;
/
--step3:调用函数;expect:成功
SELECT * FROM func_rotate0016();
--step4:通过表保存结果并查询;expect:成功
drop table if exists t_rotate0016_01;

DECLARE
    con1 VARCHAR := 'A';
    con2 VARCHAR := 'B';
    sql1 VARCHAR;
BEGIN
    sql1 := '
        CREATE TEMPORARY TABLE t_rotate0016_01 AS
        SELECT *
        FROM (
            SELECT year, product, amount
            FROM t_rotate0016
        ) AS source_table
        ROTATE (SUM(amount) FOR product IN (''' || con1 || ''', ''' || con2 || '''))';
    EXECUTE sql1;
    RAISE NOTICE 'NOTICE:Result:';
    FOR row_data IN (SELECT * FROM t_rotate0016_01) LOOP
        RAISE NOTICE 'NOTICE:%', row_data;
    END LOOP;
    DROP TABLE t_rotate0016_01;
END;
/
--step5:清理环境;expect:成功
drop table if exists t_rotate0016;
drop function if exists func_rotate0016;